#! /bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive

if [[ $1 == "" ]];then
   TD_DATE=`date -d '1 days ago' "+%Y-%m-%d"`
else
   TD_DATE=$1
fi

${HIVE_HOME} -S -e "
-- 分区配置
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;

-- 意向表,拉链表
INSERT overwrite table hz03_dlp_zx_dwd.fact_customer_relationship partition(dayinfo)
-- 更新旧数据
SELECT
    cr.id,
    cr.create_date_time,
    cr.update_date_time,
    cr.deleted,
    cr.customer_id,
    cr.first_id,
    cr.belonger,
    cr.belonger_name,
    cr.initial_belonger,
    cr.distribution_handler,
    cr.business_scrm_department_id,
    cr.last_visit_time,
    cr.next_visit_time,
    cr.origin_type,
    cr.itcast_school_id,
    cr.itcast_subject_id,
    cr.intention_study_type,
    cr.anticipat_signup_date,
    cr.level,
    cr.creator,
    cr.current_creator,
    cr.creator_name,
    cr.origin_channel,
    cr.comment,
    cr.first_customer_clue_id,
    cr.last_customer_clue_id,
    cr.process_state,
    cr.process_time,
    cr.payment_state,
    cr.payment_time,
    cr.signup_state,
    cr.signup_time,
    cr.notice_state,
    cr.notice_time,
    cr.lock_state,
    cr.lock_time,
    cr.itcast_clazz_id,
    cr.itcast_clazz_time,
    cr.payment_url,
    cr.payment_url_time,
    cr.ems_student_id,
    cr.delete_reason,
    cr.deleter,
    cr.deleter_name,
    cr.delete_time,
    cr.course_id,
    cr.course_name,
    cr.delete_comment,
    cr.close_state,
    cr.close_time,
    cr.appeal_id,
    cr.tenant,
    cr.total_fee,
    cr.belonged,
    cr.belonged_time,
    cr.belonger_time,
    cr.transfer,
    cr.transfer_time,
    cr.follow_type,
    cr.transfer_bxg_oa_account,
    cr.transfer_bxg_belonger_name,
    cr.origin_type_state,
    cr.yearinfo,
    cr.monthinfo,
    cr.hourinfo,
    if(ocr.id is not null and cr.end_date='9999-99-99', date_add('{$DT_DATE}',-1), cc.end_date) as end_date,
    cr.dayinfo
from hz03_dlp_zx_dwd.fact_customer_relationship cr 
left join itcast_ods.customer_relationship ocr on ocr.id=cr.id and ocr.dt='{$DT_DATE}'
--时间限制要有按天分区才可以加上这个,不然就是全局覆盖了
where cr.dayinfo >= date_add(ocr.dt,-30) 

union all 
-- 插入更新/新增数据
SELECT
    id,
    create_date_time,
    update_date_time,
    deleted,
    customer_id,
    first_id,
    belonger,
    belonger_name,
    initial_belonger,
    distribution_handler,
    business_scrm_department_id,
    last_visit_time,
    next_visit_time,
    origin_type,
    case when itcast_school_id=0 or itcast_school_id is null
        then -1
        else itcast_school_id
    end itcast_school_id,
    case when itcast_subject_id=0 or itcast_subject_id is null
        then -1
        else itcast_subject_id
    end itcast_subject_id,
    intention_study_type,
    anticipat_signup_date,
    level,
    creator,
    current_creator,
    creator_name,
    origin_channel,
    comment,
    first_customer_clue_id,
    last_customer_clue_id,
    process_state,
    process_time,
    payment_state,
    payment_time,
    signup_state,
    signup_time,
    notice_state,
    notice_time,
    lock_state,
    lock_time,
    itcast_clazz_id,
    itcast_clazz_time,
    payment_url,
    payment_url_time,
    ems_student_id,
    delete_reason,
    deleter,
    deleter_name,
    delete_time,
    course_id,
    course_name,
    delete_comment,
    close_state,
    close_time,
    appeal_id,
    tenant,
    total_fee,
    belonged,
    belonged_time,
    belonger_time,
    transfer,
    transfer_time,
    follow_type,
    transfer_bxg_oa_account,
    transfer_bxg_belonger_name,
    case when origin_type IN ('NETSERVICE', 'PRESIGNUP')
         then 1
         else 0
    end origin_type_state,
    substr('${TD_DATE}',1,4) as yearinfo,
    substr('${TD_DATE}',1,7) as monthinfo,
    substr('${TD_DATE}',1,13) as hourinfo,
    '9999-99-99' as end_date,
    substr('${TD_DATE}',1,10) as dayinfo
from itcast_ods.customer_relationship where deleted=0 and  dt='${TD_DATE}';


--线索表,拉链表
insert overwrite table hz03_dlp_zx_dwd.fact_customer_clue partition(start_date)
-- 更新旧数据
select 
    cc.id,
    cc.create_date_time,
    cc.update_date_time,
    cc.deleted,
    cc.customer_id,
    cc.customer_relationship_id,
    cc.session_id,
    cc.sid,
    cc.status,
    cc.users,
    cc.create_time,
    cc.platform,
    cc.s_name,
    cc.seo_source,
    cc.seo_keywords,
    cc.ip,
    cc.referrer,
    cc.from_url,
    cc.landing_page_url,
    cc.url_title,
    cc.to_peer,
    cc.manual_time,
    cc.begin_time,
    cc.reply_msg_count,
    cc.total_msg_count,
    cc.msg_count,
    cc.comment,
    cc.finish_reason,
    cc.finish_user,
    cc.end_time,
    cc.platform_description,
    cc.browser_name,
    cc.os_info,
    cc.area,
    cc.country,
    cc.province,
    cc.city,
    cc.creator,
    cc.name,
    cc.idcard,
    cc.phone,
    cc.itcast_school_id,
    cc.itcast_school,
    cc.itcast_subject_id,
    cc.itcast_subject,
    cc.wechat,
    cc.qq,
    cc.email,
    cc.gender,
    cc.level,
    cc.origin_type,
    cc.information_way,
    cc.working_years,
    cc.technical_directions,
    cc.customer_state,
    cc.valid,
    cc.anticipat_signup_date,
    cc.clue_state,
    cc.scrm_department_id,
    cc.superior_url,
    cc.superior_source,
    cc.landing_url,
    cc.landing_source,
    cc.info_url,
    cc.info_source,
    cc.origin_channel,
    cc.course_id,
    cc.course_name,
    cc.zhuge_session_id,
    cc.is_repeat,
    cc.tenant,
    cc.activity_id,
    cc.activity_name,
    cc.follow_type,
    cc.shunt_mode_id,
    cc.shunt_employee_group_id,
    cc.clue_state_stat,
    if(occ.id is not null and cc.end_date='9999-99-99',date_add('${TD_DATE}',-1),cc.end_date) as end_date,
    cc.start_date
from hz03_dlp_zx_dwd.fact_customer_clue cc 
left join itcast_ods.customer_clue occ on occ.id=cc.id and occ.dt='${TD_DATE}'
--时间限制要有按天分区才可以加上这个,不然就是全局覆盖了
where cc.start_date >= date_add(occ.dt,-30)   

union all
--插入新增和更新的数据
select 
    id,
    create_date_time,
    update_date_time,
    deleted,
    customer_id,
    customer_relationship_id,
    session_id,
    sid,
    status,
    users,
    create_time,
    platform,
    s_name,
    seo_source,
    seo_keywords,
    ip,
    referrer,
    from_url,
    landing_page_url,
    url_title,
    to_peer,
    manual_time,
    begin_time,
    reply_msg_count,
    total_msg_count,
    msg_count,
    comment,
    finish_reason,
    finish_user,
    end_time,
    platform_description,
    browser_name,
    os_info,
    area,
    country,
    province,
    city,
    creator,
    name,
    idcard,
    phone,
    itcast_school_id,
    itcast_school,
    itcast_subject_id,
    itcast_subject,
    wechat,
    qq,
    email,
    gender,
    level,
    origin_type,
    information_way,
    working_years,
    technical_directions,
    customer_state,
    valid,
    anticipat_signup_date,
    clue_state,
    scrm_department_id,
    superior_url,
    superior_source,
    landing_url,
    landing_source,
    info_url,
    info_source,
    origin_channel,
    course_id,
    course_name,
    zhuge_session_id,
    is_repeat,
    tenant,
    activity_id,
    activity_name,
    follow_type,
    shunt_mode_id,
    shunt_employee_group_id,
    case when clue_state='VALID_NEW_CLUES'
        then 1
        when clue_state='VALID_PUBLIC_NEW_CLUE'
        then 0
        else -1
    end clue_state_stat,
    '9999-99-99' as end_date,
    '${TD_DATE}' as start_date
from itcast_ods.customer_clue where dt='${TD_DATE}' and deleted = 'false';


-- 不需要查看历史快照的表, 全量覆盖插入
-- 客户表
INSERT overwrite table hz03_dlp_zx_dwd.dim_customer partition(dt)
SELECT
    *
from itcast_ods.customer;

--学科表
insert overwrite table hz03_dlp_zx_dwd.dim_itcast_subject partition(dt)
select
    case when id=0 or id is null
        then -1
        else id
    end id,
    create_date_time,
    update_date_time,
    deleted,
    name,
    code,
    tenant,
    dt
from itcast_ods.itcast_subject;

--itcast_school校区表
insert overwrite table hz03_dlp_zx_dwd.dim_itcast_school partition(dt)
select
    case when id=0 or id is null
        then -1
        else id
    end id,
    create_date_time,
    update_date_time,
    deleted,
    name,
    code,
    tenant,
    dt
from itcast_ods.itcast_school;

--部门表
INSERT overwrite table hz03_dlp_zx_dwd.dim_scrm_department partition(dt)
SELECT
    *
from itcast_ods.scrm_department;

--员工表
INSERT overwrite table hz03_dlp_zx_dwd.dim_employee partition(dt)
SELECT
    *
from itcast_ods.employee;

--线索申诉表
insert overwrite table hz03_dlp_zx_dwd.fact_customer_appeal partition(dt)
select
    *
from itcast_ods.customer_appeal where appeal_status !=1;
"
